import os
import xlrd
import sys
import django
import datetime
from xlrd import xldate_as_tuple

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.extend([BASE_DIR, ])
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "finance_c2c.settings")
django.setup()
import xlwt
from apps.product.models import Product
from apps.order.models import BaseOrder
from apps.sales_man.models import Channel, ChannelProduct
from django.db.models import Sum
from core.tools.mysql_connect import SqlModel


# 结算表
class ExportOrder(SqlModel):
    def __init__(self, start_time=None, end_time=None):
        super(ExportOrder, self).__init__()
        self.start_time = start_time
        self.end_time = end_time
        self.order_list = self.order_list()
        self.product_list = self.distinct_product()
        self.channel = Channel.objects.all()
        self.header_line = []

    def order_list(self):
        """
        订单列表
        :return:
        """
        base_order = BaseOrder.objects.all()
        if self.end_time:
            base_order = base_order.filter(create_time__lte=self.end_time)
        if self.start_time:
            base_order = base_order.filter(create_time__gt=self.start_time)
        return base_order

    def distinct_product(self):
        """
        产品去重
        :return: [1,2,3,4, product_id]
        """
        product_list = self.order_list.distinct().values("product")
        product_list = [i.get("product") for i in product_list]
        print(product_list)
        return product_list

    def distinct_channel(self):
        sql = ""

    def get_product_name_list(self):
        """
        获取去重的产品名称，便于excel 表头用
        :return:
        """
        extend_header_line = []
        for product in self.product_list:
            if Product.objects.get(id=product).parent_product:
                extend_header_line.append(Product.objects.get(id=product).parent_product.name)
            else:
                extend_header_line.append(Product.objects.get(id=product).name)
        return extend_header_line

    def write_header(self, sheet):
        leader_row = 0
        for header in self.header_line:
            sheet.write(0, leader_row, header)
            leader_row += 1

    def write_excel_sheet1(self):
        order_list = self.order_list
        product_list = self.product_list
        book = xlwt.Workbook()  # 创建excel对象
        # 写表头
        sheet1 = book.add_sheet('sheet1')  # 添加一个表
        header_line = ["日期", "姓名", "所属团队", "直属上级", "收入名称"]
        base_header_length = len(header_line)
        extend_header_line = self.get_product_name_list()
        header_line.extend(extend_header_line)
        self.header_line = header_line
        self.write_header(sheet1)

        row = 1
        for i in order_list:
            sheet1.write(row, 0, i.create_time.strftime("%Y-%m-%d"))
            name = i.salesman.name if i.salesman.name else i.salesman.account
            if i.salesman.leader:
                leader_name = i.salesman.leader.name if i.salesman.leader.name else i.salesman.leader.account
            else:
                leader_name = "无上级"
            sheet1.write(row, 1, name)
            sheet1.write(row, 2, i.salesman.channel.name)
            sheet1.write(row, 3, leader_name)
            sheet1.write(row, 4, "直推奖")
            product_id = i.product.id
            index = product_list.index(product_id)
            product_index = base_header_length + index
            sheet1.write(row, product_index, 1)
            row += 1
        return book

        # book.save("order_data.xls")

    def get_price_type(self, product_obj, channel_obj):
        price_record = ChannelProduct.objects.get(product=product_obj, channel=channel_obj).priceupdate_set.first()
        return price_record

    def write_excel_sheet2(self, book):
        sheet2 = book.add_sheet('sheet2')  # 添加一个表
        header_line = ["团队名称"]
        base_header_length = len(header_line)
        extend_header_line = self.get_product_name_list()
        header_line.extend(extend_header_line)
        self.header_line = header_line
        self.write_header(sheet2)
        row = 1
        for i in self.channel:
            orders = self.order_list.filter(salesman__channel=i)
            if orders:
                sheet2.write(row, 0, i.name)
            for index, product in enumerate(self.product_list):
                product_index = base_header_length + index
                product_obj = Product.objects.get(id=product)
                orders = self.order_list.filter(salesman__channel=i, product=product_obj)
                if orders:
                    price_record = self.get_price_type(product_obj, i)
                    if price_record.price_type == 1:
                        income = orders.count()
                    else:
                        income = orders.aggregate(Sum("income")).get("income__sum")
                        income = income / price_record.price
                    sheet2.write(row, product_index, income)
            row += 1
        return book


def write_data():
    start_time = "2018-10-01"
    end_time = "2018-11-01"
    export_order = ExportOrder(start_time=start_time, end_time=end_time)
    book = export_order.write_excel_sheet1()
    # book = export_order.write_excel_sheet2(book)
    book.save("close-account-%s.xls" % start_time[:7])


if __name__ == '__main__':
    write_data()
